After completing this lesson, you’ll be able to:
Learning content in the FME Academy presents a user's story addressing their data integration challenges with FME. You should follow along with their actions using your installation of FME (2023.0 or later) or request an on-demand virtual machine in the footer link below. Some lessons will require you to follow their steps or take additional steps to answer a quiz question.
A Resources section will provide you with links to interactive tutorials and starting workspaces when necessary.
Sven works as a Planning Analyst for a city’s Economic Development department. He has been given an Excel spreadsheet containing point locations of businesses and needs to load it into an Esri geodatabase. He will use this business data to create guides for each city neighborhood to provide to residents, prospective business owners, and tourists.
Before beginning a new project with FME, he knows it is essential to consider how people interact with the FME platform. FME users can fall into one or multiple user roles:
For this project, Sven is an author. He will create a workspace for himself using FME Form and doesn’t plan on sharing it. Maybe later on, he will upload it to FME Flow for others to use, but not yet.
Sven is starting with an Excel workbook (BusinessOwners.xlsx) with a single sheet. Each row is a separate business and has information about the business, including the name of the owner, the company name, the business license number, and the location of the business’s primary address. He wants to keep all this information in his new geodatabase. Here are two sample rows of his data.
|
First |
Last Name |
Company |
License Number |
Longitude |
Latitude |
|
Elvis |
Clay |
Diam Industries |
B347A2 |
-123.101472 |
49.2480941 |
|
Noelani |
Curry |
Mus Donec Associates |
1991FF |
-123.1318356 |
49.28042851 |
Sven begins his data integration project by opening FME Workbench (2023.0 or later) and clicking New to create a new workspace in FME.
Most FME Academy courses assume you have access to FME Form and FME Flow. You should follow along with their actions using your own installation of FME (2023.0 or later) or request an on-demand virtual machine. Some lessons contain an additional exercise challenging you to take additional steps on your own.
If you need access to FME, you can:
- Request an on-demand virtual machine to gain temporary access to FME
- Ask your administrator for a license
If you don’t have access to FME, some courses provide step-by-step tutorials you can follow to see the workflow in action. However, some Quizzes will require FME access to answer the questions.
All the data you need for the training is provided as links in the relevant lessons. If you want to download sample data, you can use the FME data repository (also on GitHub). Unless otherwise stated, the data used here originates from open data made available by the City of Vancouver in British Columbia. It contains information licensed under the Open Government License - Vancouver.
Clicking Blank Workspace opens the canvas, where a translation can be created. The canvas is currently blank, but a reader can be easily added to read the Excel dataset into FME.
A reader is a component of a workspace that enables you to read a particular dataset.
On the Start tab, you can browse recent workspaces, favorite them, and download workspaces directly from FME Flow.
Sven types “Excel” on the canvas. When Sven starts typing, the Quick Add dialog suggests objects that match “Excel.” It lists all objects you can add to the canvas: transformers, readers, and writers. Under the Readers heading, Sven double-clicks the Microsoft Excel reader.
⭐New for FME 2023.1: we've made several improvemets to the Quick Add dialog to better connect FME Workbench to the FME Hub. The FME Hub lets you discover new transformers, templates, web connections, and custom formats created by our users and add them to your FME setup. You can learn more in the documentation.
The Quick Add dialog now shows all formats and transformers from the FME Hub under one "FME Hub" header. We now show a badge indicated the trust level for each transformer publisher. These can be "Official," provided by Safe Software, "Community," prepared by our users, and coming soon, "Verified," prepared by our users and verified by Safe Software. FME Hub transformers now also show the publisher name with a link to the publisher page and the format/transformer help on the Hub. FME Hub content displays a download count, so you can ascertain Hub content popularity. Finally, we show a badge saying "Installed" for Hub content you have installed.
The Add Reader dialog appears. The Format is already set as Excel, but the other parameters still need to be set. Sven sets the Dataset parameter to the location of the Excel file, pasting in a URL: https://s3.amazonaws.com/FMEData/FMEData/Data/Planning/BusinessOwners.xlsx.
The Dataset parameter can accept one or more paths to files stored on your computer, URLs, or a connection to web service (via clicking the downward pointing arrow and then Select File From Web).
Sven then clicks on the Parameters… button.
The Microsoft Excel Parameters dialog controls how the Excel file will be read, including which sheet(s) to read. The Preview section displays how FME currently sees the data, while the Attributes section displays the attributes (spreadsheet columns, in this case) that FME has detected. The Longitude and Latitude attributes have been automatically detected as X and Y coordinates and set appropriately (under the Type column). They can be set manually if necessary. FME will automatically create points using these attributes when the spreadsheet is read.
Some datasets store geometry information and some do not. In this case, the source Excel file contained spatial data (latitude and longitude coordinates) describing the location of the address associated with each business license. However, in Excel these coordinates are just stored as numbers. To create a spatial dataset that can be analyzed and manipulated using FME or a GIS, the dataset needs to store geometry separate from its attributes.
The parameters look correct, so Sven hits OK to close the Excel Parameters dialog. The final parameter to set is the Coord. System parameter.
The Coord. System parameter sets the coordinate system of the data. For some formats, the coordinate system information is contained in the data itself, and FME can read that automatically. The Coord. System currently says “Unknown”, so Sven needs to set it.
Setting the coordinate system is not mandatory. It is necessary to set the coordinate system to use background maps when inspecting the data, to compare the data to other datasets in different coordinate systems, or to write to formats that require a coordinate system. FME will use the coordinate system stored by the dataset, if it exists. The Coord. System parameter will display “Read from source” if the dataset is capable of storing coordinate system information and will display “Unknown” if it is not. If you are unsure of the coordinate system of your data, check the metadata or contact the creator of the dataset.
Sven wants to use a background map when inspecting the data, so he sets the Coord. System to “LL84”, a commonly used global coordinate system.
This coordinate system corresponds to the common 1984 World Geodetic System used by Global Positioning Systems (GPS).
Now that all required parameters are set, Sven hits OK to close the dialog and add the reader to the canvas. The canvas now shows the single worksheet from the Excel spreadsheet: BusinessOwners.
When you add a reader to FME Workbench, you choose which feature types you want to add to the canvas. A feature type in FME corresponds to a single sheet in a spreadsheet, table in a database, or a single layer (geometry plus attributes) in spatial data. For nested markup formats like JSON or XML, users create feature types by extracting a portion of the data or exploding it into a table. Because the Excel file only has one worksheet, Sven sees a single feature type added to his canvas. Feature types are children of readers or writers, so we will usually refer to them as reader feature types or writer feature types.
Feature types contain features. For spreadsheet or database data, a feature corresponds to a single row. For spatial data, it corresponds to a single piece of geometry (point, line, polygon, etc.) and its attributes.
Here is a visual example of how FME components relate to Excel components. In the image below:
The components of a workspace are represented like this in FME Workbench. In the image below:
Sven saves the workspace by clicking the Save button in the toolbar (the row of icons right below the menu bar) and using the default values provided.
FME Workbench saves files called workspaces. They have the file ending .fmw and tend to be quite small in size, as they do not contain data from readers and writers. Instead, they just contain references to where that data is located, e.g., a file path, URL, or FME Web or Database Connection.
Make sure you are saving an .fmw file and not overwriting any data.
If you want to include data with your workspace, you can use File > Save as Template. Workspace templates have the file ending .fmwt and can optionally include data used in the workspace. These are useful for sharing entire workflows with colleagues for testing or troubleshooting.
Now Sven can run the workspace.
If you are following along, feel free to save your workspace wherever you like. We recommend saving often, including every time before you run your workspace. Note that workspace files do not contain any datasets.
Sven clicks on Run in the toolbar to run the translation.
A Translation Parameter Values dialog appears to confirm some parameters. This dialog can be helpful if Sven wants to change parameters before he runs his workspace.
Being prompted to fill in the parameter values can be useful if you want to rerun a workspace with different parameters. For example, you may be converting several datasets using the same workspace running multiple times, or testing if a workspace runs successfully with different input data. You may disable this prompt by clicking the drop-down triangle beside Run in the toolbar and deselecting Prompt for User Parameters.
After the workspace runs and the data is read, the Translation Log appears and reports what FME did during the translation and whether the translation was successful or not.
The Translation Log is displayed as a sortable table. Click the hyperlinked transformer name to navigate to the element on the canvas producing the message. This ability to identify where errors are occurring makes debugging workspaces more efficient.
On the BusinessOwners feature type, a copy of all the spreadsheet's features has been cached and can be inspected. Caches store all the features from a particular port, represented by the green magnifying glass icon. Sven clicks on the green magnifying glass icon to inspect the cached data.
Visual Preview displays a table containing all of the spreadsheet data. The total number of rows (features) is shown in the bottom right of Table View. Graphics View displays the spatial data, which in this case are points.
Visual Preview is an embedded version of a standalone program included with FME Form, the FME Data Inspector. You can use this separate program if you prefer to have a full-screen application when inspecting data. The FME Data Inspector can view any format that FME can read.
⭐New for FME 2023.0: we've significantly improved performance for 2D data visualization in Visual Preview and FME Data Inspector.
Sven notices that the BusinessOwners name on the feature type is truncated. He resizes it by double-clicking on its right edge.
Now it’s your turn! Follow Sven's steps above to add the BusinessOwners feature type. Sven also wants to include data about public art in his neighborhood guides. Help him by adding another Excel reader to connect to all the sheets (feature types) in this public art Excel workbook. The steps are the same; just use the new URL, and don't forget to set Coord. System to LL84 again. Tip: you don’t need to download the file; you can just paste the URL into the Dataset parameter of the Add Reader dialog.
The workbook contains one sheet per neighborhood. When prompted, add all the feature types. Each row is a public art installation and contains information about the location, the title of the piece, and its longitude and latitude. Your canvas should now look like the image below.
